#!/usr/bin/env python3
"""
Excel解析器模块
- 自动识别平台（B站/抖音/小红书）
- 解析Excel并映射为标准格式
- 数据验证和清洗
"""

import pandas as pd
import re
from typing import Dict, List, Tuple
from pathlib import Path


class ExcelParser:
    """Excel解析器"""

    # 平台识别特征
    PLATFORM_FEATURES = {
        'bilibili': {'UP主', 'BVID', '视频网址', '收藏夹名称'},
        'douyin': {'购物车信息', '商品ID', '音频源网址', '是否购物车'},
        'xiaohongshu': {'作品文案', '专辑名称'},
    }

    # 平台代码映射
    PLATFORM_CODE = {
        'bilibili': 0,
        'douyin': 1,
        'xiaohongshu': 2,
    }

    # 作品类型映射
    WORK_TYPE_MAP = {
        '视频': 0,
        '图文': 1,
        '音频': 2,
        '其他': 3,
    }

    def detect_platform(self, excel_path: str) -> Tuple[str, int]:
        """
        自动识别Excel平台

        Returns:
            (platform_name, platform_code)
        """
        df = pd.read_excel(excel_path, nrows=0)
        headers = set(df.columns)

        # B站特征
        if headers & self.PLATFORM_FEATURES['bilibili']:
            return 'bilibili', 0

        # 抖音特征
        if headers & self.PLATFORM_FEATURES['douyin']:
            return 'douyin', 1

        # 小红书特征
        if headers & self.PLATFORM_FEATURES['xiaohongshu']:
            return 'xiaohongshu', 2

        raise ValueError(f"无法识别Excel平台: {excel_path}")

    def parse(self, excel_path: str, platform_code: int) -> List[Dict]:
        """
        解析Excel文件

        Args:
            excel_path: Excel文件路径
            platform_code: 平台代码 (0-B站, 1-抖音, 2-小红书)

        Returns:
            作品列表
        """
        df = pd.read_excel(excel_path)
        works = []

        for idx, row in df.iterrows():
            try:
                work = self._map_row_to_work(row, platform_code, idx + 2)
                if work:
                    works.append(work)
            except Exception as e:
                print(f"警告: 解析第{idx+2}行失败: {str(e)}")

        print(f"成功解析 {len(works)}/{len(df)} 条记录")
        return works

    def _map_row_to_work(self, row: pd.Series, platform_code: int, row_num: int) -> Dict:
        """映射单行数据到作品字典"""

        # 基础信息
        work = {
            'workId': str(row.get('作品id', '')).strip(),
            'title': str(row.get('作品标题', '')).strip(),
            'workType': self.WORK_TYPE_MAP.get(row.get('作品类型'), 3),
            'platform': platform_code,
            'excelRowNum': row_num,
        }

        # 验证必填字段
        if not work['workId'] or not work['title']:
            return None

        # 作者信息
        work['author'] = self._safe_str(row.get('作品作者'))
        work['authorUid'] = self._safe_str(row.get('作者uid'))
        work['authorFansCount'] = self._safe_int(row.get('作者粉丝数量'))
        work['authorSecUid'] = self._safe_str(row.get('作者secUid'))
        work['authorHomepage'] = self._safe_str(row.get('作者主页'))
        work['homepageRemark'] = self._safe_str(row.get('主页备注'))

        # 作品内容
        work['duration'] = self._safe_str(row.get('作品时长'))
        work['durationSeconds'] = self._safe_int(row.get('作品时长秒'))
        work['workUrl'] = self._safe_str(row.get('作品网址'))
        work['coverUrl'] = self._safe_str(row.get('封面网址'))
        work['publishTime'] = self._safe_datetime(row.get('发布时间'))
        work['topicContent'] = self._safe_str(row.get('话题内容'))
        work['quality'] = self._safe_str(row.get('作品质量'))
        work['workRemark'] = self._safe_str(row.get('作品备注'))

        # 搜索信息
        work['extractType'] = self._safe_str(row.get('提取类型'))
        work['searchKeyword'] = self._safe_str(row.get('搜索关键词'))

        # 互动数据
        work['playCount'] = self._safe_int(row.get('播放量'))
        work['likeCount'] = self._safe_int(row.get('点赞量'))
        work['commentCount'] = self._safe_int(row.get('评论量'))
        work['collectCount'] = self._safe_int(row.get('收藏量'))
        work['shareCount'] = self._safe_int(row.get('分享量'))

        # 下载相关
        download_status = self._safe_str(row.get('下载状态'))
        work['downloadStatus'] = download_status
        work['isDownloaded'] = self._parse_download_status(download_status)
        work['savePath'] = self._safe_str(row.get('保存路径'))
        work['disableDownload'] = row.get('DisableDownload') in ['True', True]

        # 视频相关
        work['videoSourceUrl'] = self._safe_str(row.get('视频源网址'))
        work['videoId'] = self._safe_str(row.get('video_id'))

        # 图文相关
        work['imageDownloadUrls'] = self._safe_json(row.get('图文下载网址'))
        work['imageMusicUrl'] = self._safe_str(row.get('图文音乐下载网址'))

        # B站独有
        if platform_code == 0:
            work['collectionName'] = self._safe_str(row.get('收藏夹名称'))
            work['videoDownloadUrls'] = self._safe_json(row.get('视频下载网址列表'))

        # 抖音独有
        if platform_code == 1:
            work['searchTopic'] = self._safe_str(row.get('搜索话题'))
            work['collectionTitle'] = self._safe_str(row.get('合集名称'))
            work['collectionRemark'] = self._safe_str(row.get('合集备注'))
            work['keywordRemark'] = self._safe_str(row.get('关键词备注'))
            work['isFullscreen'] = row.get('是否全屏') == '是'
            work['hasShoppingCart'] = row.get('是否购物车') == '是'
            work['shoppingCartInfo'] = self._safe_str(row.get('购物车信息'))
            work['productId'] = self._safe_str(row.get('商品ID'))
            work['productTitle'] = self._safe_str(row.get('商品标题'))
            work['audioSourceUrl'] = self._safe_str(row.get('音频源网址'))
            work['isVipWork'] = row.get('是否会员作品') in ['True', True]

        # 小红书独有
        if platform_code == 2:
            work['albumName'] = self._safe_str(row.get('专辑名称'))
            work['contentText'] = self._safe_str(row.get('作品文案'))

        return work

    def _safe_str(self, value) -> str:
        """安全转换为字符串"""
        if pd.isna(value) or value is None:
            return None
        return str(value).strip()

    def _safe_int(self, value) -> int:
        """安全转换为整数"""
        if pd.isna(value) or value is None:
            return 0
        try:
            return int(float(value))
        except:
            return 0

    def _safe_datetime(self, value) -> str:
        """安全转换为日期时间字符串"""
        if pd.isna(value) or value is None:
            return None
        try:
            if isinstance(value, str):
                return value
            return value.isoformat()
        except:
            return None

    def _safe_json(self, value):
        """安全处理JSON字段"""
        if pd.isna(value) or value is None:
            return None
        if isinstance(value, str):
            try:
                import json
                return json.loads(value)
            except:
                return value
        return value

    def _parse_download_status(self, status_str: str) -> bool:
        """解析下载状态"""
        if not status_str:
            return False

        # B站、抖音视频: "100%"
        if status_str == "100%":
            return True

        # 小红书图文: "10张图"
        if re.match(r'^\d+张图$', status_str):
            return True

        return False


if __name__ == '__main__':
    # 测试代码
    parser = ExcelParser()

    # 测试平台识别
    test_files = [
        'backend/cool_service/docs/B 站平台_提取作品数据2025年10月17日16时22分25秒.xlsx',
        'backend/cool_service/docs/抖音平台_提取作品数据2025年10月20日10时44分31秒.xlsx',
        'backend/cool_service/docs/小红书_取作品数据2025年10月23日11时41分15秒.xlsx',
    ]

    for file in test_files:
        try:
            platform_name, platform_code = parser.detect_platform(f'../{file}')
            print(f"{file} => {platform_name} (code={platform_code})")
        except Exception as e:
            print(f"{file} => 错误: {e}")
